*******************************************************************************
*set working directory
global folder ".."


cd $folder\temp

************************************************************************************
* Data preparation
************************************************************************************

/*
import sas "$folder/data/fund_summary2", clear
keep if mofd(caldt)==tm(2019m12)
g etf_dummy=(et_flag!="")
g inst_dummy=(inst_fund=="Y")
g index_dummy=(index_fund_flag!="")

foreach var of varlist etf_dummy inst_dummy index_dummy {
bysort crsp_portno: egen temp=max(`var') 
replace `var'=temp if crsp_portno!=.
drop temp 
}
keep crsp_portno crsp_obj_cd lipper_obj_cd etf_dummy inst_dummy index_dummy
duplicates drop crsp_portno, force
save "fund_obj.dta", replace

import sas "$folder/data/fund_summary2", clear
keep if mofd(caldt)==tm(2019m12)
keep crsp_portno crsp_fundno
duplicates drop 
duplicates drop  crsp_fundno, force
save "link_crsp_portno_crsp_fundno.dta", replace

import sas "$folder/data/monthly_tna_ret_nav", clear
keep if mofd(caldt)>=tm(2019m12)
merge m:1 crsp_fundno using  "link_crsp_portno_crsp_fundno.dta"
keep if _m==3
drop _m

g tm = mofd(caldt)
format tm %tm

collapse (sum) mtna (mean) mret, by(crsp_portno tm)

sort crsp_portno tm
bysort crsp_portno: g flow=(mtna[_n]-(1+mret)*mtna[_n-1])/mtna[_n-1]
sum flow, d
winsor2 flow,replace cuts(1 99)
save "crsp_portno_flow.dta", replace

use "$folder/data\security_level_analysis/holdings_2019Q4_2020Q2.dta", clear
keep crsp_portno report_dt
keep if report_dt >= td(31dec2019) & report_dt<= td(30apr2020)
duplicates drop
bysort crsp_portno: g N=_N
tab N
keep if N==5
keep crsp_portno
duplicates drop
save "crsp_portno_monthly_reporter.dta", replace


// security level pecking order
use "$folder/data\security_level_analysis/holdings_2019Q4_2020Q2.dta", clear
keep if report_dt >= td(31dec2019) 
keep crsp_portno report_dt percent_tna cusip coupon maturity_dt nbr_shares
drop if missing(cusip)

* merge with fund obj
merge m:1 crsp_portno using  "fund_obj"
keep if _merge==3 // many are equity funds
drop _merge 
keep if substr(crsp_obj_cd,1,1)=="I" | lipper_obj_cd=="LP"
drop if substr(crsp_obj_cd,1,2)=="IM" // drop MMF
//drop if substr(crsp_obj_cd,1,2)=="IU" // drop municipal funds

drop if missing(maturity)
g maturity_year = round((maturity - td(31dec2019))/365)
replace maturity_year=0 if maturity_year<0
replace maturity_year=30 if maturity_year>30 

*merge with bond ratings
merge m:1 cusip using "$folder/temp/Mergent_issues_ratings.dta"
keep if _merge==3
drop _merge 

g bond_category="Gov" if substr(bond_type,1,1)=="U"|substr(bond_type,1,1)=="O"
replace bond_category="Corp" if bond_type=="CDEB"

g liquidity_group = rating_num
replace liquidity_group=11 if (substr(bond_type,1,1)=="U"|substr(bond_type,1,1)=="O") & maturity_year>=10
replace liquidity_group=12 if (substr(bond_type,1,1)=="U"|substr(bond_type,1,1)=="O") & maturity_year>=1 & maturity_year<10
replace liquidity_group=13 if (substr(bond_type,1,1)=="U"|substr(bond_type,1,1)=="O") & maturity_year<1
drop if missing(liquidity_group)

g caldt = report_dt
bysort caldt crsp_portno : egen sum_percent_tna=sum(percent_tna)

sum sum_percent_tna, d
*keep if sum_percent_tna>50 // drop funds which have more than 50 percent assets uncovered by mergent 


collapse (sum) sum_percent_tna_by_liquidity=percent_tna (mean) sum_percent_tna, by(caldt crsp_portno liquidity_group)
g sum_percent_tna_by_category_norm=sum_percent_tna_by_liquidity/sum_percent_tna
gsort caldt crsp_portno -liquidity_group
by caldt crsp_portno: g rank = sum(sum_percent_tna_by_category_norm)
keep caldt crsp_portno liquidity_group rank sum_percent_tna
duplicates drop
save rank, replace




// merge data
use "$folder/data\security_level_analysis/holdings_2019Q4_2020Q2.dta", clear
keep if report_dt >= td(31dec2019) & report_dt<= td(30apr2020)
keep crsp_portno report_dt percent_tna cusip coupon maturity_dt nbr_shares market_val
drop if missing(cusip)

* merge with fund obj
merge m:1 crsp_portno using  "fund_obj"
keep if _merge==3 // many are equity funds
drop _merge 
keep if substr(crsp_obj_cd,1,1)=="I" | lipper_obj_cd=="LP"
drop if substr(crsp_obj_cd,1,2)=="IM" // drop MMF
//drop if substr(crsp_obj_cd,1,2)=="IU" // drop municipal funds


*merge with lpi of 2019Q4
merge m:1 crsp_portno using  "fund_lpi_2019"
keep if _merge==3 // many are equity funds
drop _merge 

*merge with bond ratings
merge m:1 cusip using "Mergent_issues_ratings_outstanding.dta"
drop if _merge==2
drop _merge 

*merge with the list of funds which report monthly
merge m:1 crsp_portno using "crsp_portno_monthly_reporter"
tab _m
keep if _m==3
drop _m


*merge with montly flows
g tm = mofd(report_dt)
merge m:1 crsp_portno tm using "crsp_portno_flow"
keep if _m==3
drop _m

g maturity_year = round((maturity_dt-td(31dec2019))/365)
drop if maturity_year<0

* merge with rank
g caldt = report_dt
g liquidity_group = rating_num
replace liquidity_group=11 if (substr(bond_type,1,1)=="U"|substr(bond_type,1,1)=="O") & maturity_year>=10
replace liquidity_group=12 if (substr(bond_type,1,1)=="U"|substr(bond_type,1,1)=="O") & maturity_year>=1 & maturity_year<10
replace liquidity_group=13 if (substr(bond_type,1,1)=="U"|substr(bond_type,1,1)=="O") & maturity_year<1

merge m:1 crsp_portno caldt liquidity_group  using "rank"
drop if _m==2
drop _m

sort crsp_portno cusip report_dt

egen id=group(crsp_portno cusip)
duplicates drop id tm, force

*encode cusip, g(cusip_n)

tsset id tm
tsfill, full 


foreach var of varlist asset_liquidity rating_num {
bysort id: carryforward `var', gen(temp)
drop `var'
rename temp `var'
}
replace percent_tna=0 if percent_tna==.
replace nbr_shares=0 if nbr_shares==.

sort id tm
format tm %tm
bysort  id : g sell_percent_tna = -(percent_tna-percent_tna[_n-1])
winsor2 sell_percent_tna, cut(1 99) replace
sum sell_percent_tna, d

bysort id: g shares_lag = nbr_shares[_n-1]
bysort id: g sell_pct_mid = -(nbr_shares-nbr_shares[_n-1])/ ((nbr_shares+nbr_shares[_n-1])/2)
bysort id: g sell_pct_ini = -(nbr_shares-nbr_shares[_n-1])/ (nbr_shares[_n-1])
winsor2 sell_pct_ini, cut(1 99) suffix(_win)
winsor2 sell_pct_mid, cut(1 99) suffix(_win)


bysort id: g sell_dummy = (nbr_shares<nbr_shares[_n-1])

g crisis = (tm==tm(2020m3))
g fund_illiquidity = (1-asset_liquidity)*100
winsor2 fund_illiquidity, cut(1 99) replace

g crisis_illiquidity=crisis*fund_illiquidity

g redemption = (-flow)
replace redemption=0 if redemption<0


encode crsp_obj_cd, g(crsp_obj_cd_n)

g fund_type = ""
replace fund_type = "Govt" if  substr(crsp_obj_cd,1,2)=="IG"
replace fund_type = "Corp" if  substr(crsp_obj_cd,1,2)=="IC"
replace fund_type = "MS" if  substr(crsp_obj_cd,1,2)=="I"
replace fund_type = "BL" if  lipper_obj_cd=="LP"

drop if fund_type==""

g ln_size = log(mtna)
 
g reg_no = rating_num if bond_type=="CDEB" 
replace reg_no=-9 if reg_no>=-11 & reg_no<=-9 & bond_type=="CDEB" 
replace reg_no=reg_no+10 
replace reg_no=21 if (substr(bond_type,1,1)=="U"|substr(bond_type,1,1)=="O")



save holdings_reg, replace
*/



************************************************************************************
* Graph
************************************************************************************
{


* opend
use holdings_reg, clear
drop if reg_no==-2
keep if etf==0 

mat T = J(21,2,.) // MAT T2 is for the graph

forvalues i=1/21{	

reg sell_pct_ini_win redemption  maturity_year ln_size mret     if tm==tm(2020m3) &    reg_no==`i', ab(crsp_obj_cd_n)

	mat T[`i',1] =_b[redemption]
	mat T[`i',2] = _se[redemption]
	
}

mat colnames T = mu se


* DO GRAPHS WITH BANDS NOW
clear
svmat T, names( col )

gen min95=mu-1.96*se
gen max95=mu+1.96*se

gen decile=_n

twoway (line mu decile, lpattern(dash) lcolor(navy)) (scatter mu decile, graphregion(color(white)) bgcolor(white) mcolor(navy) msymbol(square) msize(small)) (rcap min95 max95 decile, lcolor(navy)), ///
xlabel(1  "<CCC-"  2 "CCC-" 3 "CCC" 4 "CCC+" 5 "B-" 6 "B" 7 "B+" 8 "BB-" 9 "BB" 10 "BB+" 11 "BBB-" 12 "BBB" 13 "BBB+" 14 "A-" 15 "A" 16 "A+" 17 "AA-" 18 "AA" 19 "AA+" 20 "AAA" 21 "Treasury",angle(vertical)) xtitle("") legend(off)  ylabel(,nogrid)  ytitle("") //yline(1, lpattern(dot) lcolor(black))
graph export  "$\folder\output\liquidation_redemption_open.pdf", replace


* active
use holdings_reg, clear
drop if substr(crsp_obj_cd,1,2)=="IG"
drop if reg_no==-2
keep if etf==0 & index==0
drop if  maturity_dt<td(31mar2020) // drop bonds that will mature in 2020m3

mat T = J(21,2,.) // MAT T2 is for the graph

forvalues i=1/21{	

reg sell_pct_ini_win redemption  maturity_year ln_size mret  if tm==tm(2020m3) &    reg_no==`i', ab(crsp_obj_cd_n)
	mat T[`i',1] =_b[redemption]
	mat T[`i',2] = _se[redemption]
	
}

mat colnames T = mu se


clear
svmat T, names( col )

gen min95=mu-1.96*se
gen max95=mu+1.96*se

gen decile=_n

twoway (line mu decile, lpattern(dash) lcolor(navy)) (scatter mu decile, graphregion(color(white)) bgcolor(white) mcolor(navy) msymbol(square) msize(small)) (rcap min95 max95 decile, lcolor(navy)), ///
xlabel(1  "<CCC-"  2 "CCC-" 3 "CCC" 4 "CCC+" 5 "B-" 6 "B" 7 "B+" 8 "BB-" 9 "BB" 10 "BB+" 11 "BBB-" 12 "BBB" 13 "BBB+" 14 "A-" 15 "A" 16 "A+" 17 "AA-" 18 "AA" 19 "AA+" 20 "AAA" 21 "Treasury",angle(vertical)) xtitle("") legend(off)  ylabel(,nogrid)  ytitle("") //yline(1, lpattern(dot) lcolor(black))
*graph export  "$\folder\output\liquidation_redemption_active.pdf", replace

*save Figure10rawdata, replace



* index
use holdings_reg, clear
drop if reg_no==-2
keep if etf==0 & index==1


mat T = J(21,2,.) // MAT T2 is for the graph

forvalues i=11/21{	

reg sell_pct_ini_win redemption  maturity_year ln_size mret  if tm==tm(2020m3) &    reg_no==`i', ab(crsp_obj_cd_n)

capture: 	mat T[`i',1] =_b[redemption]
capture: 	mat T[`i',2] = _se[redemption]
	
}

mat colnames T = mu se


* DO GRAPHS WITH BANDS NOW
clear
svmat T, names( col )

gen min95=mu-1.96*se
gen max95=mu+1.96*se

gen decile=_n


twoway (line mu decile, lpattern(dash) lcolor(navy)) (scatter mu decile, graphregion(color(white)) bgcolor(white) mcolor(navy) msymbol(square) msize(small)) (rcap min95 max95 decile, lcolor(navy)), ///
xlabel(1  "<CCC-"  2 "CCC-" 3 "CCC" 4 "CCC+" 5 "B-" 6 "B" 7 "B+" 8 "BB-" 9 "BB" 10 "BB+" 11 "BBB-" 12 "BBB" 13 "BBB+" 14 "A-" 15 "A" 16 "A+" 17 "AA-" 18 "AA" 19 "AA+" 20 "AAA" 21 "Treasury",angle(vertical)) xtitle("") legend(off)  ylabel(,nogrid)  ytitle("") //yline(1, lpattern(dot) lcolor(black))
graph export  "$\folder\output\liquidation_redemption_index.pdf", replace



* ETF
use holdings_reg, clear
drop if reg_no==-2
keep if etf==1
drop if  maturity_dt<td(31mar2020) // drop bonds that will mature in 2020m3

mat T = J(21,2,.) // MAT T2 is for the graph

forvalues i=1/21{	

reg sell_pct_ini_win redemption  maturity_year ln_size mret  if tm==tm(2020m3) &    reg_no==`i', ab(crsp_obj_cd_n)

	mat T[`i',1] =_b[redemption]
	mat T[`i',2] = _se[redemption]
	
}

mat colnames T = mu se


* DO GRAPHS WITH BANDS NOW
clear
svmat T, names( col )

gen min95=mu-1.96*se
gen max95=mu+1.96*se

gen decile=_n

twoway (line mu decile, lpattern(dash) lcolor(navy)) (scatter mu decile, graphregion(color(white)) bgcolor(white) mcolor(navy) msymbol(square) msize(small)) (rcap min95 max95 decile, lcolor(navy)), ///
xlabel(1  "<CCC-"  2 "CCC-" 3 "CCC" 4 "CCC+" 5 "B-" 6 "B" 7 "B+" 8 "BB-" 9 "BB" 10 "BB+" 11 "BBB-" 12 "BBB" 13 "BBB+" 14 "A-" 15 "A" 16 "A+" 17 "AA-" 18 "AA" 19 "AA+" 20 "AAA" 21 "Treasury",angle(vertical)) xtitle("") legend(off)  ylabel(,nogrid)  ytitle("") //yline(1, lpattern(dot) lcolor(black))
graph export  "$\folder\output\liquidation_redemption_etf.pdf", replace




*********************************************
use holdings_reg, clear
drop if reg_no==-2
drop if  maturity_dt<td(31mar2020) // drop bonds that will mature in 2020m3
sort crsp_portno cusip report_dt

bysort crsp_portno cusip: g sell_amount = -(nbr_shares-nbr_shares[_n-1])/(nbr_shares[_n-1])*market_val[_n-1]


collapse (sum) sell_amount market_val nbr_shares, by(reg_no tm) 
tsset reg_no tm

bysort reg_no: g sell_pct_mid_win = sell_amount/ (market_val[_n-1])*100

g position_chg = - sell_pct
twoway bar position_chg reg_no if tm ==tm(2020m3),   graphregion(color(white)) bgcolor(white) ///
xlabel(1  "<CCC-"  2 "CCC-" 3 "CCC" 4 "CCC+" 5 "B-" 6 "B" 7 "B+" 8 "BB-" 9 "BB" 10 "BB+" 11 "BBB-" 12 "BBB" 13 "BBB+" 14 "A-" 15 "A" 16 "A+" 17 "AA-" 18 "AA" 19 "AA+" 20 "AAA" 21 "Treasury",angle(vertical)) xtitle("")ytitle("Position change (%)") legend(off)  ylabel(,nogrid)  //yline(1, lpattern(dot) lcolor(black))
graph export  "$\folder\output\liquidation_by_rating.pdf", replace


*********************************************
use holdings_reg, clear
drop liquidity_group
g liquidity_group = rating_num
drop if liquidity_group==-12
replace liquidity_group=-9 if liquidity_group>=-11 & liquidity_group<=-9 
replace liquidity_group=11 if (substr(bond_type,1,1)=="U"|substr(bond_type,1,1)=="O") & maturity_year>=10
replace liquidity_group=11 if (substr(bond_type,1,1)=="U"|substr(bond_type,1,1)=="O") & maturity_year>=1 & maturity_year<10
replace liquidity_group=11 if (substr(bond_type,1,1)=="U"|substr(bond_type,1,1)=="O") & maturity_year<1
drop if missing(liquidity_group)


*drop if substr(crsp_obj_cd,1,2)=="IG"
replace reg_no = liquidity_group+10
drop if etf==1 // drop ETF
drop if reg_no==-2
drop if  maturity_dt<td(31mar2020) // drop bonds that will mature in 2020m3
sort crsp_portno cusip report_dt

bysort crsp_portno cusip: g sell_amount = -(nbr_shares-nbr_shares[_n-1])/(nbr_shares[_n-1])*market_val[_n-1]


collapse (sum) sell_amount market_val nbr_shares, by(reg_no tm) 
tsset reg_no tm

bysort reg_no: g sell_pct_mid_win = sell_amount/ (market_val[_n-1])*100

g position_chg = - sell_pct
twoway bar position_chg reg_no if tm ==tm(2020m3),   graphregion(color(white)) bgcolor(white) ///
xlabel(1  "<CCC-"  2 "CCC-" 3 "CCC" 4 "CCC+" 5 "B-" 6 "B" 7 "B+" 8 "BB-" 9 "BB" 10 "BB+" 11 "BBB-" 12 "BBB" 13 "BBB+" 14 "A-" 15 "A" 16 "A+" 17 "AA-" 18 "AA" 19 "AA+" 20 "AAA" 21 "Treasury",angle(vertical)) xtitle("")ytitle("Position change (%)") legend(off)  ylabel(,nogrid)  //yline(1, lpattern(dot) lcolor(black))


graph export  "$\folder\output\liquidation_by_rating_no_etf.pdf", replace



*********************************************
use holdings_reg, clear
drop if etf==1 // drop ETF
drop if reg_no==-2
drop if  maturity_dt<td(31mar2020) // drop bonds that will mature in 2020m3
keep if index_dummy==1 // keep index funds
sort crsp_portno cusip report_dt

bysort crsp_portno cusip: g sell_amount = -(nbr_shares-nbr_shares[_n-1])/(nbr_shares[_n-1])*market_val[_n-1]


collapse (sum) sell_amount market_val nbr_shares, by(reg_no tm) 
tsset reg_no tm

bysort reg_no: g sell_pct_int = sell_amount/ (market_val[_n-1])*100

g position_chg = - sell_pct
twoway bar position_chg reg_no if tm ==tm(2020m3),   graphregion(color(white)) bgcolor(white) ///
xlabel(1  "<CCC-"  2 "CCC-" 3 "CCC" 4 "CCC+" 5 "B-" 6 "B" 7 "B+" 8 "BB-" 9 "BB" 10 "BB+" 11 "BBB-" 12 "BBB" 13 "BBB+" 14 "A-" 15 "A" 16 "A+" 17 "AA-" 18 "AA" 19 "AA+" 20 "AAA" 21 "Treasury",angle(vertical)) xtitle("")ytitle("Position change (%)") legend(off)  ylabel(,nogrid)  //yline(1, lpattern(dot) lcolor(black))
graph export  "$\folder\output\liquidation_by_rating_index.pdf", replace





*********************************************
use holdings_reg, clear
drop if etf==1 // drop ETF
drop if reg_no==-2
drop if  maturity_dt<td(31mar2020) // drop bonds that will mature in 2020m3
keep if index_dummy==0 // keep active funds
sort crsp_portno cusip report_dt

bysort crsp_portno cusip: g sell_amount = -(nbr_shares-nbr_shares[_n-1])/(nbr_shares[_n-1])*market_val[_n-1]


collapse (sum) sell_amount market_val nbr_shares, by(reg_no tm) 
tsset reg_no tm

bysort reg_no: g sell_pct_int = sell_amount/ (market_val[_n-1])*100

g position_chg = - sell_pct
twoway bar position_chg reg_no if tm ==tm(2020m3),   graphregion(color(white)) bgcolor(white) ///
xlabel(1  "<CCC-"  2 "CCC-" 3 "CCC" 4 "CCC+" 5 "B-" 6 "B" 7 "B+" 8 "BB-" 9 "BB" 10 "BB+" 11 "BBB-" 12 "BBB" 13 "BBB+" 14 "A-" 15 "A" 16 "A+" 17 "AA-" 18 "AA" 19 "AA+" 20 "AAA" 21 "Treasury",angle(vertical)) xtitle("")ytitle("Position change (%)") legend(off)  ylabel(,nogrid)  //yline(1, lpattern(dot) lcolor(black))
graph export  "$\folder\output\liquidation_by_rating_active.pdf", replace




*********************************************
use holdings_reg, clear
keep if etf==1 // drop ETF
drop if reg_no==-2
drop if  maturity_dt<td(31mar2020) // drop bonds that will mature in 2020m3
keep if index_dummy==1 // keep index 
sort crsp_portno cusip report_dt

bysort crsp_portno cusip: g sell_amount = -(nbr_shares-nbr_shares[_n-1])/(nbr_shares[_n-1])*market_val[_n-1]


collapse (sum) sell_amount market_val nbr_shares, by(reg_no tm) 
tsset reg_no tm

bysort reg_no: g sell_pct_int = sell_amount/ (market_val[_n-1])*100

g position_chg = - sell_pct
twoway bar position_chg reg_no if tm ==tm(2020m3),   graphregion(color(white)) bgcolor(white) ///
xlabel(1  "<CCC-"  2 "CCC-" 3 "CCC" 4 "CCC+" 5 "B-" 6 "B" 7 "B+" 8 "BB-" 9 "BB" 10 "BB+" 11 "BBB-" 12 "BBB" 13 "BBB+" 14 "A-" 15 "A" 16 "A+" 17 "AA-" 18 "AA" 19 "AA+" 20 "AAA" 21 "Treasury",angle(vertical)) xtitle("")ytitle("Position change (%)") legend(off)  ylabel(,nogrid)  //yline(1, lpattern(dot) lcolor(black))
graph export  "$\folder\output\liquidation_by_rating_etf.pdf", replace




* Liquidity measure
use "liquidity_bond_tm", clear
keep if date>=tm(2019m10) & date<=tm(2019m12) 
collapse (mean) turnover-size_neg, by(cusip_id)
g cusip_8 = substr(cusip, 1, 8)
drop cusip_id
rename cusip_8 cusip
save "liquidity_2019Q4", replace

use  "liquidity_2019Q4", clear
merge 1:1 cusip using "$folder/temp/Mergent_issues_ratings.dta"
keep if _merge==3
drop _merge 

drop if missing(maturity)
g maturity_year = round((maturity - td(31dec2019))/365)
replace maturity_year=0 if maturity_year<0
replace maturity_year=30 if maturity_year>30 


g liquidity_group = rating_num
drop if liquidity_group==-12
replace liquidity_group=-9 if liquidity_group>=-11 & liquidity_group<=-9 
replace liquidity_group=11 if (substr(bond_type,1,1)=="U"|substr(bond_type,1,1)=="O") & maturity_year>=10
replace liquidity_group=11 if (substr(bond_type,1,1)=="U"|substr(bond_type,1,1)=="O") & maturity_year>=1 & maturity_year<10
replace liquidity_group=11 if (substr(bond_type,1,1)=="U"|substr(bond_type,1,1)=="O") & maturity_year<1
drop if missing(liquidity_group)



collapse (median )turnover-size_neg, by(liquidity_group)
replace  liquidity_group=liquidity_group+10


foreach var of varlist Amihud Roll IRC {
twoway bar `var' liquidity_group, graphregion(color(white)) bgcolor(white) ///
 xtitle("")ytitle("`var'") legend(off)  ylabel(,nogrid)  /// yline(1, lpattern(dot) lcolor(black))
 xlabel(1  "<CCC-"  2 "CCC-" 3 "CCC" 4 "CCC+" 5 "B-" 6 "B" 7 "B+" 8 "BB-" 9 "BB" 10 "BB+" 11 "BBB-" 12 "BBB" 13 "BBB+" 14 "A-" 15 "A" 16 "A+" 17 "AA-" 18 "AA" 19 "AA+" 20 "AAA" 21 "Treasury",angle(vertical)) ///
 xtitle("")legend(off)  ylabel(,nogrid)  //yline(1, lpattern(dot) lcolor(black))
graph export  "$\folder\output\liquidity_`var'.pdf", replace
}

/*
foreach var of varlist Amihud Roll IRC {
twoway bar `var' liquidity_group, graphregion(color(white)) bgcolor(white) ///
 xtitle("")ytitle("`var'") legend(off)  ylabel(,nogrid)  /// yline(1, lpattern(dot) lcolor(black))
 xlabel(1  "<CCC-"  2 "CCC-" 3 "CCC" 4 "CCC+" 5 "B-" 6 "B" 7 "B+" 8 "BB-" 9 "BB" 10 "BB+" 11 "BBB-" 12 "BBB" 13 "BBB+" 14 "A-" 15 "A" 16 "A+" 17 "AA-" 18 "AA" 19 "AA+" 20 "AAA" 21 "TBond" 22 "TNote" 23 "Tbill",angle(vertical)) xtitle("")ytitle("Position change (%)") legend(off)  ylabel(,nogrid)  //yline(1, lpattern(dot) lcolor(black))
graph export  "$\folder\output\liquidity_`var'.pdf", replace
}
*/
}


************************************************************************************
* Regression
************************************************************************************
use holdings_reg, clear
sort crsp_portno cusip tm
by crsp_portno cusip: g L_percent_tna=percent_tna[_n-1]
keep if tm==tm(2020m3)
merge m:1 crsp_portno cusip using pecking_order_crsp_portno_cusip
keep if _m==3
drop _m

sum rank, d
g high_rank=(rank<`r(p50)')

g act=( index==0 & etf==0)

replace sell_pct_ini_win=sell_pct_ini_win*100
replace sell_pct_mid_win=sell_pct_mid_win*100

replace redemption=redemption*100
encode cusip, g(cusip_n)

g first_to_liquidate = (1-rank)
g redemption_first_to_liquidate=redemption*first_to_liquidate
label var first_to_liquidate "Rank"
label var redemption_first_to_liquidate "Outflows*Rank"
label var L_percent_tna "Initial position"
label var redemption_first_to_liquidate "Outflows*Rank"
label var coupon "Coupon rate"
label var maturity_year "Maturity"
label var redemption "Outflows"
 
set more off
eststo clear
local contr 
local y sell_pct_ini_win
local x redemption  redemption_first_to_liquidate 
eststo:reghdfe `y'  `x' `contr'  if etf==0,  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if  index==0&etf==0,  ab(cusip_n  crsp_obj_cd_n)  vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if  index==1&etf==0,  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if  etf==1,  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
esttab
esttab using  "$\folder\output\liquidation_redemption_pecking_order.tex", ///
 replace b(3) se(3) star(* 0.10 ** 0.05 *** 0.01  )   ///
mtitles("\shortstack{Open-end\\All}" "\shortstack{Open-end\\Active}" "\shortstack{Open-end\\Passive}"  "\shortstack{\\ETF}" ) ///
ar2  br  label nocon  nonotes  s(FE1  FE2 N r2_a, ///
label( "Bond F.E."  "Fund objective F.E."  "Observations" "Adj. R-squared") ///
 fmt( 0  0  %9.0fc 3 )) width(\hsize) 
 
 
 
 set more off
eststo clear
local contr 
local y sell_pct_ini_win
local x redemption  redemption_first_to_liquidate 
eststo:reghdfe `y'  `x' `contr'  if etf==0,  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if  index==0&etf==0,  ab(cusip_n  crsp_obj_cd_n)  vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if  index==1&etf==0,  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
esttab
esttab using  "$\folder\output\liquidation_redemption_pecking_order_no_etf.tex", ///
 replace b(3) se(3) star(* 0.10 ** 0.05 *** 0.01  )   ///
mtitles("\shortstack{Open-end\\All}" "\shortstack{Open-end\\Active}" "\shortstack{Open-end\\Passive}"  "\shortstack{\\ETF}" ) ///
ar2  br  label nocon  nonotes  s(FE1  FE2 N r2_a, ///
label( "Bond F.E."  "Fund objective F.E."  "Observations" "Adj. R-squared") ///
 fmt( 0  0  %9.0fc 3 )) width(\hsize) 
 
 
  set more off
eststo clear
local contr 
local y sell_pct_ini_win
local x redemption  redemption_first_to_liquidate 
eststo:reghdfe `y'  `x' `contr'  if etf==0,  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "No"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if  index==0&etf==0,  ab(cusip_n  crsp_obj_cd_n)  vce(robust)
estadd local FE1 "No"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if  index==1&etf==0,  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "No"
estadd local FE2 "Yes"
esttab
esttab using  "$revisiontable\liquidation_redemption_pecking_order_no_etf_no_bond_fe.tex", ///
 replace b(3) se(3) star(* 0.10 ** 0.05 *** 0.01  )   ///
mtitles("\shortstack{Open-end\\All}" "\shortstack{Open-end\\Active}" "\shortstack{Open-end\\Passive}"  "\shortstack{\\ETF}" ) ///
ar2  br  label nocon  nonotes  s(FE1  FE2 N r2_a, ///
label( "Bond F.E."  "Fund objective F.E."  "Observations" "Adj. R-squared") ///
 fmt( 0  0  %9.0fc 3 )) width(\hsize) 
 
 
 
 set more off
eststo clear
local contr 
local y sell_pct_ini_win
local x redemption  redemption_first_to_liquidate first_to_liquidate
eststo:reghdfe `y'  `x' `contr'  if etf==0,  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if  index==0&etf==0,  ab(cusip_n  crsp_obj_cd_n)  vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if  index==1&etf==0,  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if  etf==1,  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
esttab
esttab using  "$\folder\output\liquidation_redemption_pecking_order_wt_intercept.tex", ///
 replace b(3) se(3) star(* 0.10 ** 0.05 *** 0.01  )   ///
mtitles("\shortstack{Open-end\\All}" "\shortstack{Open-end\\Active}" "\shortstack{Open-end\\Passive}"  "\shortstack{\\ETF}" ) ///
ar2  br  label nocon  nonotes  s(FE1  FE2 N r2_a, ///
label( "Bond F.E."  "Fund objective F.E."  "Observations" "Adj. R-squared") ///
 fmt( 0  0  %9.0fc 3 )) width(\hsize) 
 
 
************************************************************************************
* seperate IG and HY
************************************************************************************
 set more off
eststo clear
local contr 
local y sell_pct_ini_win
local x redemption  redemption_first_to_liquidate 
eststo:reghdfe `y'  `x' `contr'  if etf==0 & rating_num>=1 & rating_num!= . & bond_cate=="Corp",  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if  index==0&etf==0 & rating_num>=1 & rating_num!=.& bond_cate=="Corp",  ab(cusip_n  crsp_obj_cd_n)  vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if  index==1&etf==0 & rating_num>=1 & rating_num!=.& bond_cate=="Corp",  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
esttab
esttab using  "$revisiontable\liquidation_redemption_pecking_order_no_etf_IG.tex", ///
 replace b(3) se(3) star(* 0.10 ** 0.05 *** 0.01  )   ///
mtitles("\shortstack{Open-end\\All}" "\shortstack{Open-end\\Active}" "\shortstack{Open-end\\Passive}"  "\shortstack{\\ETF}" ) ///
ar2  br  label nocon  nonotes  s(FE1  FE2 N r2_a, ///
label( "Bond F.E."  "Fund objective F.E."  "Observations" "Adj. R-squared") ///
 fmt( 0  0  %9.0fc 3 )) width(\hsize) 

 
 
  set more off
eststo clear
local contr 
local y sell_pct_ini_win
local x redemption  redemption_first_to_liquidate 
eststo:reghdfe `y'  `x' `contr'  if etf==0 & rating_num<1 & rating_num!=. & bond_cate=="Corp",  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if  index==0&etf==0 & rating_num<1 & rating_num!=. & bond_cate=="Corp",  ab(cusip_n  crsp_obj_cd_n)  vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if  index==1&etf==0 & rating_num<1 & rating_num!=. & bond_cate=="Corp",  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
esttab
esttab using  "$revisiontable\liquidation_redemption_pecking_order_no_etf_HY.tex", ///
 replace b(3) se(3) star(* 0.10 ** 0.05 *** 0.01  )   ///
mtitles("\shortstack{Open-end\\All}" "\shortstack{Open-end\\Active}" "\shortstack{Open-end\\Passive}"  "\shortstack{\\ETF}" ) ///
ar2  br  label nocon  nonotes  s(FE1  FE2 N r2_a, ///
label( "Bond F.E."  "Fund objective F.E."  "Observations" "Adj. R-squared") ///
 fmt( 0  0  %9.0fc 3 )) width(\hsize) 


use holdings_reg, clear
merge m:1 cusip tm using "treasury_type"
drop if _m==2
drop _m


replace sell_pct_ini_win=sell_pct_ini_win*100
replace sell_dummy=sell_dummy*100

replace redemption=redemption*100
replace mret=mret*100


label var coupon "Coupon rate"
label var maturity_year "Maturity"
label var redemption "Outflows"
label var ln_size "Fund size"
label var mret "Security return"

egen cusip_n=group(cusip) 

g offering_month = mofd(offering_date)
format offering_month %tm
g new_issue=(tm(2019m12)-offering_month<=12)
g less_than_one_year = (maturity_year<1)

g bond_age = (tm(2019m12)-offering_month)/12
 
g maturity_at_issue_corp = round((maturity-offering_date)/365,1)
 
 * treasury all
set more off
eststo clear
local contr 
local y sell_pct_ini_win
local x redemption  
local contr  ln_size mret
eststo:reghdfe `y'  `x' `contr'  if  tm==tm(2020m3) &  Tbill==1 ,  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if  tm==tm(2020m3) &  on_the_run== 1& Tnote==1,  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if  tm==tm(2020m3) &  on_the_run== 0& Tnote==1,  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if  tm==tm(2020m3) &  on_the_run== 1& Tbond==1,  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if  tm==tm(2020m3) &  on_the_run== 0& Tbond==1,  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
esttab
esttab using  "$\folder\output\liquidation_treasury_m3.tex", ///
 replace b(3) se(3) star(* 0.10 ** 0.05 *** 0.01  )   ///
 mtitles("\shortstack{T-bill\\all}"  "\shortstack{T-note\\on-the-run}" "\shortstack{T-note\\off-the-run}" ///
 "\shortstack{T-bond\\on-the-run }"  "\shortstack{T-bond\\off-the-run}") ///
ar2  br  label nocon  nonotes  s(FE1 FE1  FE2 N r2_a, ///
label( "Control" "Bond F.E."  "Fund objective F.E."  "Observations" "Adj. R-squared") ///
 fmt(0 0  0  %9.0fc 3 )) width(\hsize) drop(`contr')
 

* treasury without control
set more off
eststo clear
local contr 
local y sell_pct_ini_win
local x redemption  
local contr   
eststo:reghdfe `y'  `x' `contr'  if  tm==tm(2020m3) &  Tbill==1 ,  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if  tm==tm(2020m3) &  on_the_run== 1& Tnote==1,  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if  tm==tm(2020m3) &  on_the_run== 0& Tnote==1,  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if  tm==tm(2020m3) &  on_the_run== 1& Tbond==1,  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if  tm==tm(2020m3) &  on_the_run== 0& Tbond==1,  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
esttab
esttab using  "$revisiontable\liquidation_treasury_m3_no_control.tex", ///
 replace b(3) se(3) star(* 0.10 ** 0.05 *** 0.01  )   ///
 mtitles("\shortstack{T-bill\\all}"  "\shortstack{T-note\\on-the-run}" "\shortstack{T-note\\off-the-run}" ///
 "\shortstack{T-bond\\on-the-run }"  "\shortstack{T-bond\\off-the-run}") ///
ar2  br  label nocon  nonotes  s( FE1  FE2 N r2_a, ///
label( "Bond F.E."  "Fund objective F.E."  "Observations" "Adj. R-squared") ///
 fmt( 0  0  %9.0fc 3 )) width(\hsize) drop(`contr')

 

* treasury without fe
g one = 1

set more off
eststo clear
local contr 
local y sell_pct_ini_win
local x redemption  
local contr  ln_size mret
eststo:reghdfe `y'  `x' `contr'  if  tm==tm(2020m3) &  Tbill==1 ,  ab(one) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if  tm==tm(2020m3) &  on_the_run== 1& Tnote==1,  ab(one) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if  tm==tm(2020m3) &  on_the_run== 0& Tnote==1,  ab(one) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if  tm==tm(2020m3) &  on_the_run== 1& Tbond==1,  ab(one) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if  tm==tm(2020m3) &  on_the_run== 0& Tbond==1,  ab(one) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
esttab
esttab using  "$revisiontable\liquidation_treasury_m3_no_fe.tex", ///
 replace b(3) se(3) star(* 0.10 ** 0.05 *** 0.01  )   ///
 mtitles("\shortstack{T-bill\\all}"  "\shortstack{T-note\\on-the-run}" "\shortstack{T-note\\off-the-run}" ///
 "\shortstack{T-bond\\on-the-run }"  "\shortstack{T-bond\\off-the-run}") ///
ar2  br  label nocon  nonotes  s(FE1  N r2_a, ///
label( "Control"  "Observations" "Adj. R-squared") ///
 fmt(0   %9.0fc 3 )) width(\hsize) drop(`contr')

 
 
 
 * treasury on the run
 
set more off
eststo clear
local contr 
local y sell_pct_ini_win
local x redemption  
local contr  ln_size mret
local condition & on_the_run==1
*eststo:reghdfe `y'  `x' `contr'  if  tm==tm(2020m3) &  Tbill==1  `condition',  ab(cusip_n  crsp_obj_cd_n) vce(robust)
*estadd local FE1 "Yes"
*estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if  tm==tm(2020m3) &  maturity_at_issue==2 `condition',  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if  tm==tm(2020m3) &  maturity_at_issue==3 `condition',  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if  tm==tm(2020m3) &  maturity_at_issue==5 `condition',  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if  tm==tm(2020m3) &  maturity_at_issue==7 `condition',  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if  tm==tm(2020m3) &  maturity_at_issue==10 `condition',  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
*eststo:reghdfe `y'  `x' `contr'  if  tm==tm(2020m3) &  maturity_at_issue==20 `condition',  ab(cusip_n  crsp_obj_cd_n) vce(robust)
*estadd local FE1 "Yes"
*estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if  tm==tm(2020m3) &  maturity_at_issue==30 `condition',  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
esttab
esttab using  "$\folder\output\liquidation_treasury_by_maturity_on_the_run.tex", ///
 replace b(3) se(3) star(* 0.10 ** 0.05 *** 0.01  )   ///
 mtitles( "Two" "Three" "Five" "Seven" "Ten" "Thirty") ///
ar2  br  label nocon  nonotes  s(FE1 FE1  FE2 N r2_a, ///
label( "Control" "Bond F.E."  "Fund objective F.E."  "Observations" "Adj. R-squared") ///
 fmt(0 0  0  %9.0fc 3 )) width(\hsize) drop(`contr')
 
 
 
 
 
 
 
 
 
 

 * treasury 
set more off
eststo clear
local contr 
local y sell_pct_ini_win
local x redemption  
local contr  ln_size mret
local condition & on_the_run==0
*eststo:reghdfe `y'  `x' `contr'  if  tm==tm(2020m3) &  Tbill==1  `condition',  ab(cusip_n  crsp_obj_cd_n) vce(robust)
*estadd local FE1 "Yes"
*estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if  tm==tm(2020m3) &  maturity_at_issue==2 `condition',  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if  tm==tm(2020m3) &  maturity_at_issue==3 `condition',  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if  tm==tm(2020m3) &  maturity_at_issue==5 `condition',  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if  tm==tm(2020m3) &  maturity_at_issue==7 `condition',  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if  tm==tm(2020m3) &  maturity_at_issue==10 `condition',  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
*eststo:reghdfe `y'  `x' `contr'  if  tm==tm(2020m3) &  maturity_at_issue==20 `condition',  ab(cusip_n  crsp_obj_cd_n) vce(robust)
*estadd local FE1 "Yes"
*estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if  tm==tm(2020m3) &  maturity_at_issue==30 `condition',  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
esttab
esttab using  "$\folder\output\liquidation_treasury_by_maturity_off_the_run.tex", ///
 replace b(3) se(3) star(* 0.10 ** 0.05 *** 0.01  )   ///
 mtitles( "Two" "Three" "Five" "Seven" "Ten"  "Thirty") ///
ar2  br  label nocon  nonotes  s(FE1 FE1  FE2 N r2_a, ///
label( "Control" "Bond F.E."  "Fund objective F.E."  "Observations" "Adj. R-squared") ///
 fmt(0 0  0  %9.0fc 3 )) width(\hsize) drop(`contr')
 
 
 
 
 
 * corporate by age
 set more off
eststo clear
local contr 
local y sell_pct_ini_win
local x redemption  
local contr  ln_size mret
local condition & etf==0 & index==0
eststo:reghdfe `y'  `x' `contr'  if tm==tm(2020m3) & bond_age<1 & liquidity_group<=10& liquidity_group>=-10 `condition',  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if tm==tm(2020m3) & bond_age>=1 & bond_age<5 & liquidity_group<=10& liquidity_group>=-10 `condition',  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if tm==tm(2020m3) & bond_age>=5 & bond_age<=10 & liquidity_group<=10& liquidity_group>=-10 `condition',  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if tm==tm(2020m3) & bond_age>10 & liquidity_group>=-10 `condition',  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"

esttab
esttab using  "$revisiontable\liquidation_corp_m3_by_age.tex", ///
 replace b(3) se(3) star(* 0.10 ** 0.05 *** 0.01  )   ///
mtitles( "Age$\in[0,1)$" "Age$\in[1,5)$""Age$\in[5,10)$""Age$>10$"  ) ///
ar2  br  label nocon  nonotes  s(FE1  FE2 N r2_a, ///
label( "Bond F.E."  "Fund objective F.E."  "Observations" "Adj. R-squared") ///
 fmt( 0  0  %9.0fc 3 )) width(\hsize) drop(ln_size mret)

 
 * corporate by maturity
 set more off
eststo clear
local contr 
local y sell_pct_ini_win
local x redemption  
local contr  ln_size mret
local condition & etf==0 & index==0
eststo:reghdfe `y'  `x' `contr'  if tm==tm(2020m3) & maturity_year>-1 & maturity_year<=5 & liquidity_group<=10& liquidity_group>=-10 `condition',  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if tm==tm(2020m3) & maturity_year>5 & maturity_year<=10 & liquidity_group<=10& liquidity_group>=-10 `condition',  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if tm==tm(2020m3) & maturity_year>10 & liquidity_group<=10& liquidity_group>=-10 `condition',  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"

esttab
esttab using  "$revisiontable\liquidation_corp_m3_by_maturity.tex", ///
 replace b(3) se(3) star(* 0.10 ** 0.05 *** 0.01  )   ///
mtitles(  "Maturity$\in(0,5]$""Maturity$\in(5,10]$""Maturity$>10$"  ) ///
ar2  br  label nocon  nonotes  s(FE1  FE2 N r2_a, ///
label( "Bond F.E."  "Fund objective F.E."  "Observations" "Adj. R-squared") ///
 fmt( 0  0  %9.0fc 3 )) width(\hsize) drop(ln_size mret)
 
 * corporate by maturity
 set more off
eststo clear
local contr 
local y sell_pct_ini_win
local x redemption  
local contr  ln_size mret
local condition & etf==0 & index==0
eststo:reghdfe `y'  `x' `contr'  if tm==tm(2020m3) & maturity_at_issue_corp>-1 & maturity_at_issue_corp<=5 & liquidity_group<=10& liquidity_group>=-10 `condition',  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if tm==tm(2020m3) & maturity_at_issue_corp>5 & maturity_at_issue_corp<=10 & liquidity_group<=10& liquidity_group>=-10 `condition',  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if tm==tm(2020m3) & maturity_at_issue_corp>10 & liquidity_group<=10& liquidity_group>=-10 `condition',  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"

esttab
esttab using  "$revisiontable\liquidation_corp_m3_by_maturity_at_issue.tex", ///
 replace b(3) se(3) star(* 0.10 ** 0.05 *** 0.01  )   ///
mtitles(  "Maturity$\in(0,5]$""Maturity$\in(5,10]$""Maturity$>10$"  ) ///
ar2  br  label nocon  nonotes  s(FE1  FE2 N r2_a, ///
label( "Bond F.E."  "Fund objective F.E."  "Observations" "Adj. R-squared") ///
 fmt( 0  0  %9.0fc 3 )) width(\hsize) drop(ln_size mret)

 
 
 
 
 * corporate by rating
 set more off
eststo clear
local contr 
local y sell_pct_ini_win
local x redemption  
local contr  ln_size mret
local condition etf_dummy==1
eststo:reghdfe `y'  `x' `contr'  if tm==tm(2020m3) & rating_num>=10 & rating_num<=10 & liquidity_group<=10 & liquidity_group>=-10 `condition',  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if tm==tm(2020m3) & rating_num>=7 & rating_num<=9 & liquidity_group>=-10 `condition',  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if tm==tm(2020m3) & rating_num>=4 & rating_num<=6 & liquidity_group>=-10 `condition',  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if tm==tm(2020m3) & rating_num>=1 & rating_num<=3 & liquidity_group>=-10 `condition',  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if tm==tm(2020m3) & rating_num>=-2 & rating_num<=0 & liquidity_group>=-10 `condition',  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if tm==tm(2020m3) & rating_num>=-5 & rating_num<=-3 & liquidity_group<=10  & liquidity_group>=-10 `condition',  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if tm==tm(2020m3) & rating_num>=-8 & rating_num<=-6 & liquidity_group<=10  & liquidity_group>=-10 `condition',  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"
eststo:reghdfe `y'  `x' `contr'  if tm==tm(2020m3) & rating_num>=-10 & rating_num<=-9 & liquidity_group<=10  & liquidity_group>=-10 `condition',  ab(cusip_n  crsp_obj_cd_n) vce(robust)
estadd local FE1 "Yes"
estadd local FE2 "Yes"

esttab
esttab using  "$revisiontable\liquidation_corp_m3_by_rating.tex", ///
 replace b(3) se(3) star(* 0.10 ** 0.05 *** 0.01  )   ///
mtitles("AAA" "AA" "A" "BBB" "BB" "B" "CCC" "CC-C"   ) ///
ar2  br  label nocon  nonotes  s(FE1  FE2 N r2_a, ///
label( "Bond F.E."  "Fund objective F.E."  "Observations" "Adj. R-squared") ///
 fmt( 0  0  %9.0fc 3 )) width(\hsize) drop(ln_size mret)

 
 //Summary stats 
 g outflow = -flow*100
 label var outflow "Outflows"
 
estpost summarize outflow if on_the_run==1,  d
est store sum_stat
esttab sum_stat  using "$\folder\output\summary_stats_on_the_run.tex", replace ///
cells("mean(fmt(%9.3f)) sd(fmt(%9.3f))  p10(fmt(%9.3f)) p25(fmt(%9.3f)) p50(fmt(%9.3f)) p75(fmt(%9.3f)) p90(fmt(%9.3f)) ") ///
label booktabs nonum gaps noobs ///
collabels( "mean" "sd" "p10" "p25" "p50" "p75" "p90") width(\hsize) 

estpost summarize outflow if on_the_run==0,  d
est store sum_stat
esttab sum_stat using "$\folder\output\summary_stats_off_the_run.tex", replace ///
cells("mean(fmt(%9.3f)) sd(fmt(%9.3f))  p10(fmt(%9.3f)) p25(fmt(%9.3f)) p50(fmt(%9.3f)) p75(fmt(%9.3f)) p90(fmt(%9.3f)) ") ///
label booktabs nonum gaps noobs ///
collabels( "mean" "sd" "p10" "p25" "p50" "p75" "p90") width(\hsize) 

 ************************************************************************************
* Treasury liquidation (aggregate stats)
************************************************************************************
use "$folder/data/security_level_analysis/CRSP_treasury_2019Q4_2020Q2.dta", clear

rename *, lower
rename tcusip cusip
g tm = mofd(caldt)

sort caldt

isid cusip caldt
*bysort cusip tm: keep if _n==_N // change to monthly
bysort cusip tm: keep if _n==1 // change to monthly

g maturity_at_issue =round((tmatdt-tdatdt)/365)

g Tbill = (maturity_at_issue<=1)
g Tnote = (maturity_at_issue>1&maturity_at_issue<=10)
g Tbond = (maturity_at_issue>10)

sort caldt maturity_at_issue tdatdt
by caldt maturity_at_issue: g on_the_run=(_n==_N )

keep cusip tm tmatdt tdatdt maturity_at_issue Tbil Tnote Tbond on_the_run tdtotout

save "treasury_type", replace


use "$folder/data/security_level_analysis/CRSP_treasury_2019Q4_2020Q2.dta", clear
rename *, lower
g maturity_month =round((tmatdt-caldt)/30)
g maturity_at_issue =round((tmatdt-tdatdt)/365)

g Tbill = (maturity_at_issue<=1)
g Tnote = (maturity_at_issue>1&maturity_at_issue<=10)
g Tbond = (maturity_at_issue>10)

collapse maturity_month [iw=tdtotout],  by(Tbill Tnote Tbond )

 
 ***********************************
 * Liquidation and Trading volume
 ***********************************
use "$folder/data\security_level_analysis/holdings_2019Q4_2020Q2.dta" if  report_dt==td(31dec2019)| report_dt==td(31mar2020), clear
drop if missing(cusip)

merge m:1 crsp_portno using  "fund_obj"
keep if _m==3
 drop _merge 
 drop if etf_dummy==1

*merge with bond ratings
merge m:1 cusip using "$folder/temp/Mergent_issues_ratings.dta"
keep if _merge==3
drop _merge 

g bond_category=""

local typelist CDEB CMTN CMTZ CCOV CP USBN CS CCPI CPAS
foreach type of local typelist {
replace bond_category="Corp" if bond_type=="`type'" & bond_category==""
}

local typelist USBD USBL USNT USSI USSP USTC  O4w O13W O26W O52W O2Y O3Y O5Y O7Y O10Y CCPI IIDX
foreach type of local typelist {
replace bond_category="Gov" if bond_type=="`type'" & bond_category==""
}

g bond_category2=""

local typelist CDEB CMTN CMTZ CCOV CP USBN CS CCPI CPAS
foreach type of local typelist {
replace bond_category2="IG" if bond_type=="`type'" & bond_category2=="" & rating_num>=1 & rating_num!=.
replace bond_category2="HY" if bond_type=="`type'" & bond_category2=="" & rating_num<1 & rating_num!=.
}

local typelist  USBL   O4w O13W O26W O52W 
foreach type of local typelist {
replace bond_category2="TBill" if bond_type=="`type'" & bond_category2==""
}

local typelist USNT O2Y O3Y O5Y O7Y O10Y 
foreach type of local typelist {
replace bond_category2="TNote" if bond_type=="`type'" & bond_category2==""
}

local typelist  USBD  USSI USSP USTC CCPI IIDX
foreach type of local typelist {
replace bond_category2="TBond" if bond_type=="`type'" & bond_category2==""
}


g price_per_share = market_val/nbr_shares


drop if bond_category==""
collapse (sum) nbr_shares market_val, by(bond_category bond_category2 report_dt)

sort bond_category2 report_dt
bysort bond_category2: g position_chg = -round((nbr_shares-nbr_shares[_n-1])/10^9,.1) // change to billion
bysort bond_category2: g position_chg_price_adjusted = -round((nbr_shares-nbr_shares[_n-1])/10^9*(market_val[_n-1]/nbr_shares[_n-1]),.1) // change to billion
bysort bond_category2: g position_chg_pct = -round((nbr_shares-nbr_shares[_n-1])/nbr_shares[_n-1]*100,.1) // change to percent


matrix A = J(5, 4, 0)

local typelist TBill TNote TBond IG HY
local i=0
foreach type of local typelist  {
local i=`i'+1
summarize position_chg if bond_category2=="`type'", meanonly
matrix A[`i',1] = r(mean)
summarize position_chg_pct if bond_category2=="`type'", meanonly
matrix A[`i',2] = r(mean)
}

* outstanding
local j = 3
matrix A[1,`j']=2416.9		
matrix A[2,`j'] = 9929.2
matrix A[3,`j'] = 2379.1
matrix A[4,`j'] = round(9581.1*1143.5/(1143.5+278.8),.1)
matrix A[5,`j'] = round(9581.1*278.8/(1143.5+278.8),.1)

* trading volumn
local j = 4
matrix A[1,`j'] = 128.3
matrix A[2,`j'] = 461.9
matrix A[3,`j'] = 68.5
matrix A[4,`j'] = 21.2
matrix A[5,`j'] = 11.8

matrix colnames A = Sold Percent Outstanding Volume
matrix rownames A = T-bill T-note T-bond IG HY


esttab matrix(A), gaps
esttab matrix(A) using "$\folder\output\liquidation_trading_volume.tex", ///	
replace cells(" Sold(fmt(%9.1f)) Percent(fmt(%9.1f)) Outstanding(fmt(%9.1f)) Volume(fmt(%9.1f)) ") ///
label booktabs nonum gaps noobs nomtitles ///
collabels(  "Liquidation" "Percent of initial position" "Outstanding" "Trading volume" ) width(\hsize) 



matrix B = J(5, 4, 0)

local typelist TBill TNote TBond IG HY
local i=0
foreach type of local typelist  {
local i=`i'+1
summarize position_chg if bond_category2=="`type'", meanonly
matrix B[`i',1] = r(mean)
summarize position_chg_pct if bond_category2=="`type'", meanonly
matrix B[`i',2] = r(mean)
}

* outstanding
local j = 3
local k = 1
matrix B[1,`j'] = round(A[1,`k']/2416.9*100,.1)
matrix B[2,`j'] = round(A[2,`k']/9929.2*100,.1)
matrix B[3,`j'] = round(A[3,`k']/2379.1*100,.1)
matrix B[4,`j'] = round(A[4,`k']/(9581.1*1143.5/(1143.5+278.8))*100,.1)
matrix B[5,`j'] = round(A[5,`k']/(9581.1*278.8/(1143.5+278.8))*100,.1)

* trading volumn
local j = 4
matrix B[1,`j'] = 128.3
matrix B[2,`j'] = 461.9
matrix B[3,`j'] = 68.5
matrix B[4,`j'] = 21.2
matrix B[5,`j'] = 11.8

matrix colnames B = Sold Percent Outstanding Volume
matrix rownames B = T-bill T-note T-bond IG HY

esttab matrix(B), gaps
esttab matrix(B) using "$\folder\output\liquidation_trading_volume_v2.tex", ///	
replace cells(" Sold(fmt(%9.1f)) Percent(fmt(%9.1f)) Outstanding(fmt(%9.1f)) Volume(fmt(%9.1f)) ") ///
label booktabs nonum gaps noobs nomtitles ///
collabels(  "Liquidation" "\% of initial position" "\% of outstanding" "Trading volume" ) width(\hsize) 


